﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SenerPro;
using MensUnoShop.Discount;
using System.Data;
using System.IO;

namespace MensUnoShop.Models
{
    public class DBOrder
    {
        private DataClassesMenDataContext _dc;
        private Logger _log = new Logger(Config.logpath);
        
        

        public void SaveShoppingCart(Guid scguid, Guid mguid, DateTime _lastaccess)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                var uc = _dc.UShoppingCart.SingleOrDefault(sc => sc.SCguid == scguid && sc.SCmguid == mguid);
                if (uc != null)
                {
                    uc.SCdatein = _lastaccess;
                    uc.SCstatus = true;
                }
                else
                {
                    UShoppingCart us = new UShoppingCart();
                    us.SCdatein = _lastaccess;
                    us.SCguid = scguid;
                    us.SCmguid = mguid;
                    us.SCstatus = true;
                    _dc.UShoppingCart.InsertOnSubmit(us);
                    _dc.SubmitChanges();
                }
            }
        }

        public bool CheckTemp(Guid scguid, Guid mguid)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {

                var ss = _dc.UShoppingCart.FirstOrDefault(s => s.SCguid == scguid && s.SCmguid == mguid && s.SCstatus);
                if (ss != null)
                {
                    ss.SCstatus = false;
                    _dc.SubmitChanges();
                    return true;
                }
                else
                    return false;
            }
        }

        public string GetTxId(char y, char m, string day, char r)
        {
            string txid = string.Empty;
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                _dc.SP_CreateOrdernumber(y, m, day, r, ref txid);
            }
            return txid;
        }

        #region 新增訂單
        /// <summary>
        /// 建立新訂單、訂購者、收件者、發票資訊
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public bool CreateOrder(Order order)
        {
            bool isok = false;
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    int k = _dc.SP_CreateNewOrder(order.OGUID, order.MGUID, order.TXID,
                        order.DELIVERYCOST,(int)order.PW, order.TOTALPRICE, order.PAYPRICE, (int)Config.OrderStatusType.Init,order.SCGUID, order.SHIPPINGWAY,order.INVOICETYPE,order.INVOICETITLE,order.INVOICESERIAL);
                    
                }
                //建賣場資訊
                createOrderMarket(order.OGUID, order.MLIST);
                //建收件者
                createRecipient(order.RECIPIENT);
                isok = true;
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "[CreateOrder]",ex.ToString());
            }
            return isok;
        }


        private void createRecipient(Recipient recipient)
        {
            UOrderRecipient or = new UOrderRecipient();
            or.RCguid = recipient.REGUID;
            or.RCname = recipient.NAME;
            or.RCzip = recipient.ZIP ?? string.Empty;
            or.RCcity = recipient.CITY ?? string.Empty;
            or.RCarea = recipient.AREA ?? string.Empty;
            or.RCaddress = recipient.ADDRESS ?? string.Empty;
            or.RCtel = recipient.TEL ?? string.Empty;
            or.RCsex = recipient.SEX;
            or.RCcountry = recipient.COUNTRY ?? "";
            or.RCcustomer = recipient.CUSTOMER ?? "";
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.UOrderRecipient.InsertOnSubmit(or);
                    _dc.SubmitChanges();
                }
            }

            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "createRecipient", ex.ToString());
            }
        }

        private void createOrderMarket(Guid oguid, List<OrderMarket> mlist)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    foreach (var m in mlist)
                    {
                        UOrderMarkets om = new UOrderMarkets();
                        om.MOKguid = m.MOKGUID;
                        om.MOKOguid = oguid;
                        om.MOKimg = m.IMG;
                        om.MOKmkguid = m.MKGUID;
                        om.MOKprice = m.PRICE;
                        om.MOKsingleprice = m.SINGLEPRICE;
                        om.MOKtitle = m.TITLE;
                        om.MOKsn = m.SN;
                        om.MOKamount = m.AMOUNT;
                        om.MOKspecid = m.SPECID;
                        om.MOKpdtitle = m.COLOR;
                        om.MOKtype = (int)m.TYPE;
                        om.MOKcomboid = m.COMBOID;
                        _dc.UOrderMarkets.InsertOnSubmit(om);
                        _dc.SubmitChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "createRecipient", ex.ToString());
            }
        }

        #endregion

        public void SetDiscount(Guid oguid, Config.OrderDiscountType discountType, int deprice, string detitle)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                UOrderDiscount od = new UOrderDiscount();
                od.ODdeprice = deprice;
                od.ODOguid = oguid;
                od.ODstatus = true;
                od.ODtitle = detitle;
                _dc.UOrderDiscount.InsertOnSubmit(od);
                _dc.SubmitChanges();
            }
        }

        public bool CheckStock(Guid scguid, List<OrderMarket> mlist)
        {
            bool ischecked = true;
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                foreach (var m in mlist)
                {
                    int amount = 1;
                    if (m.TYPE == Config.MarketType.Combo)
                        amount = 1;
                    else
                        amount = m.AMOUNT;
                    var k = _dc.UShoppingCartTempSpecAmount.SingleOrDefault(sc => sc.STcartid == scguid
                                                                                      && sc.STmkguid == m.MKGUID
                                                                                      && sc.STomguid == m.MOKGUID
                                                                                      && sc.STspguid == m.SPECID
                                                                                      && sc.STamount == amount);
                    if (k == null)
                    {
                        return false;
                    }
                }
            }
            return ischecked;
        }

        public void SetStatus(Guid oguid, Config.OrderStatusType newstatus)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                _dc.SP_SetOrderStatus(oguid,(int)newstatus);
            }
        }

        public void CommitStock(Guid SCGUID, Guid OGUID)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                _dc.UShoppingCartTempSpecAmount.DeleteAllOnSubmit(_dc.UShoppingCartTempSpecAmount.Where(s => s.STcartid == SCGUID));
                _dc.SubmitChanges();
            }
        }

        public void SaveTempOrder(string CASHID, byte[] orderbinary)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                UTempOrder to = new UTempOrder();
                to.TOcashid = CASHID;
                to.TObinary = orderbinary;
                to.TOdatein = DateTime.Now;
                _dc.UTempOrder.InsertOnSubmit(to);
                _dc.SubmitChanges();
            }
        }

        public bool UptOrder(Guid OGUID, string CASHID)
        {
            bool isok = false;
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                var o = _dc.UOrders.SingleOrDefault(oo => oo.Oguid == OGUID);
                if (o != null)
                {
                    o.Ocashid = CASHID;
                    _dc.SubmitChanges();
                    isok = true;
                }
            }
            return isok;
        }

        public void FreeStock(Guid _scguid)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                int? k = 0;
                var o = _dc.SP_SetSpecFreeStock(_scguid,ref k);
                
            }
        }

        public Order GetTempOrder(string ocashid)
        {
            using (_dc = new DataClassesMenDataContext(Config.connString))
            {
                var o = _dc.UTempOrder.SingleOrDefault(to => to.TOcashid == ocashid);
                if (o != null)
                    return (Order)Tools.DeSerialize(o.TObinary.ToArray());
                else
                    return null;
            }
        }

        public Order GetOrder(string id, string types)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    UOrders od;
                    if (types == "cashid")
                        od = _dc.UOrders.SingleOrDefault(oo => oo.Ocashid == id);
                    else if (types == "txid")
                        od = _dc.UOrders.SingleOrDefault(oo => oo.Otxid == id);
                    else
                        od = _dc.UOrders.SingleOrDefault(oo => oo.Oguid == new Guid(id));
                    if (od != null)
                    {
                        Order o = new Order(od.Oguid);
                        o.CASHID = od.Ocashid;
                        o.DATEIN = od.Oorderdate;
                        o.DELIVERYCOST = od.Odelivery;
                        o.MGUID = od.OMguid;
                        o.SHIPPINGNUMBER = od.Oshippingnum;
                        o.PAYPRICE = od.Opayprice;
                        o.SHIPPINGWAY = od.Oshippingway.HasValue?od.Oshippingway.Value:0;
                        o.TOTALPRICE = od.Oorderprice;
                        o.PW = (PayType)(od.Opayway);
                        o.SCGUID = od.SCguid;
                        o.TXID = od.Otxid;
                        o.INVOICESERIAL = od.Oinvoiceserial;
                        o.INVOICETITLE = od.Oinvoicetitle;
                        o.INVOICETYPE = od.Oinvoicetype;
                        return o;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetOrder]id--[" + id + "]type--[" + types + "]\n" + ex.ToString());
            }
            return null;
        }

        public List<OrderMarket> GetOrderMarketList(Guid oguid)
        {
            List<OrderMarket> mlist = new List<OrderMarket>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var ml = _dc.UOrderMarkets.Where(m => m.MOKOguid == oguid);
                    foreach (var m in ml)
                    {
                        OrderMarket k = new OrderMarket();
                        k.AMOUNT = m.MOKamount;
                        k.IMG = m.MOKimg;
                        k.MKGUID = m.MOKmkguid;
                        k.MOKGUID = m.MOKguid;
                        k.PRICE = m.MOKprice;
                        k.SINGLEPRICE = m.MOKsingleprice;
                        k.SN = m.MOKsn;
                        k.SPECID = m.MOKspecid.Value;
                        k.TITLE = m.MOKtitle;
                        k.COLOR = m.MOKpdtitle;
                        k.TYPE = (Config.MarketType)m.MOKtype;
                        k.COMBOID = m.MOKcomboid.HasValue ? m.MOKcomboid.Value : 0;
                        mlist.Add(k);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetOrderMarketList]\n" + ex.ToString());
            }
            return mlist;
        }

        public List<Order.OrderStatus> GetStatus(Guid oguid)
        {
            List<Order.OrderStatus> oslist = new List<Order.OrderStatus>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var slist = _dc.UOrderStatus.Where(os => os.OSOguid == oguid).OrderBy(os => os.OSid);
                    foreach (var s in slist)
                    {
                        Order.OrderStatus os = new Order.OrderStatus();
                        os.Ostatus = (Config.OrderStatusType)s.OSstatus.Value;
                        os.Ochangedate = s.OSdatein;
                        oslist.Add(os);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetStatus]" + ex.ToString());
            }
            return oslist;
        }

        public List<SimpleOrder> GetOrderList(Guid mguid)
        {
            List<SimpleOrder> oslist = new List<SimpleOrder>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var slist = _dc.V_OrderList.Where(os => os.OMguid == mguid);
                    foreach (var s in slist)
                    {
                        SimpleOrder os = new SimpleOrder();
                        os.DATEIN = s.Oorderdate;
                        os.TXID = s.Otxid;
                        os.STATUS = (Config.OrderStatusType)s.Ostatus;
                        os.PRICE = s.Opayprice;
                        os.SHIPPINGNUMBER = s.Oshippingnum;
                        oslist.Add(os);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetOrderList]" + ex.ToString());
            }
            return oslist;
        }

        public Recipient GetRecipient(Guid oguid)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var s = _dc.UOrderRecipient.FirstOrDefault(os => os.RCguid == oguid);
                    if (s != null)
                    {
                        Recipient r = new Recipient(oguid);
                        r.ADDRESS = s.RCaddress;
                        r.AREA = s.RCarea;
                        r.CITY = s.RCcity;
                        r.COUNTRY = s.RCcountry;
                        r.CUSTOMER = s.RCcustomer;
                        r.NAME = s.RCname;
                        r.SEX = s.RCsex.Value;
                        r.TEL = s.RCtel;
                        r.ZIP = s.RCzip;
                        return r;

                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetRecipient]" + ex.ToString());
            }
            return new Recipient(oguid);
        }

        public List<ITradeInDiscount> GetDiscount(Guid oguid)
        {
            List<ITradeInDiscount> sdlist = new List<ITradeInDiscount>();

            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var slist = _dc.UOrderDiscount.Where(o => o.ODOguid == oguid);
                    foreach(var s in slist)
                    {
                        SimpleDiscount sm = new SimpleDiscount();
                        sm.DEPRICE = s.ODdeprice;
                        sm.SKTYPE = (Config.OrderDiscountType)s.ODtype;
                        sm.TITLE = s.ODtitle;
                        sdlist.Add(sm);
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordException(_log.DBExceptionPath, "[GetDiscount]" + ex.ToString());
            }
            return sdlist;
        }


        public List<Order> GetOrderList(string querychoice, string querycontent)
        {
            List<Order> orderList = new List<Order>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    #region
                    //StringBuilder sb = new StringBuilder();
                    //sb.Append("SELECT * FROM V_OrderData WHERE 1=1 ");
                    //if (txid != string.Empty) { sb.Append("AND Otxid={0} "); }
                    //if (cashid != string.Empty) { sb.Append("AND Ocashid={1} "); }
                    //if (email != string.Empty) { sb.Append("AND Memail={2} "); }
                    //if (mname != string.Empty) { sb.Append("AND MDfirstname + MDfirstname = {3} "); }                    

                    //var querydata = _dc.ExecuteQuery<Order>(
                    //    sb.ToString(),
                    //    txid,
                    //    cashid,
                    //    email,
                    //    mname
                    //    );

                    //foreach (var item in querydata)
                    //{
                    //    Order o = new Order(item.OGUID);
                    //    Recipient rc = new Recipient(o.OGUID);
                    //    //Member m = new Member(item);
                    //    //SimpleOrder so = new SimpleOrder();

                    //    o.TXID = item.TXID;
                    //    o.CASHID = item.CASHID;
                    //    o.PRICE = item.PRICE;
                    //    o.PAYPRICE = item.PAYPRICE;
                    //    o.PW = (PayType)item.PW;
                    //    o.DATEIN = item.DATEIN;

                    //    rc.NAME = item.RECIPIENT.NAME;
                    //    rc.TEL = item.RECIPIENT.TEL;
                    //    rc.SEX = item.RECIPIENT.SEX == null ? true : item.RECIPIENT.SEX;
                    //    rc.ZIP = item.RECIPIENT.ZIP;
                    //    rc.COUNTRY = item.RECIPIENT.COUNTRY;
                    //    rc.CITY = item.RECIPIENT.CITY;
                    //    rc.AREA = item.RECIPIENT.AREA;
                    //    rc.ADDRESS = item.RECIPIENT.ADDRESS;

                    //    o.RECIPIENT = rc;


                    //    orderList.Add(o);
                    //}

                    //return orderList;
                    #endregion

                    var me = _dc.V_OrderData.OrderByDescending(vod => vod.Oorderdate).AsQueryable();

                    switch (querychoice)
                    {
                        case "querytxid":
                            me = me.Where(vod => vod.Otxid == querycontent);
                            break;
                        case "querycashid":
                            me = me.Where(vod => vod.Ocashid == querycontent);
                            break;
                        case "queryemail":
                            me = me.Where(vod => vod.Memail == querycontent);
                            break;
                        case "querymname":
                            me = me.Where(vod => vod.MDlastname + vod.MDfirstname == querycontent);
                            break;
                        default:
                            break;
                    }
                    
                    if (me != null)
                    {
                        foreach (var item in me)
                        {
                            Order o = new Order(item.Oguid);
                            Recipient rc = new Recipient(o.OGUID);
                            Member m = new Member(item.Memail);

                            //SimpleOrder so = new SimpleOrder();

                            o.TXID = item.Otxid;
                            o.CASHID = item.Ocashid;
                            o.PRICE = item.Oorderprice;
                            o.PAYPRICE = item.Opayprice;
                            o.PW = item.COpaytype == null ? PayType.Default : (PayType)item.COpaytype;
                            o.DATEIN = item.Oorderdate;
                            o.MGUID = item.OMguid;

                            rc.NAME = item.RCname;
                            rc.TEL = item.RCtel;
                            rc.SEX = item.RCsex == null ? true : item.RCsex.Value;
                            rc.ZIP = item.RCzip;
                            rc.COUNTRY = item.MDcountry;
                            rc.CITY = item.MDcity;
                            rc.AREA = item.MDarea;
                            rc.ADDRESS = item.MDaddress;

                            o.RECIPIENT = rc;


                            orderList.Add(o);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderList", ex.ToString());
            }
            return orderList;
        }

        public List<OrderData> GetOrderdataList(string querychoice, string querycontent, string querytype)
        {
            List<OrderData> orderdataList = new List<OrderData>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    StringBuilder sb = new StringBuilder(); 
                    sb.Append("SELECT *, vod.Oorderprice as ORDERPRICE, (MDlastname + MDfirstname) as MNAME, MOKpdtitle as COLOR, ISNULL(vod.COpaytype, 0) as PAYTYPE, ");
                    sb.Append("vod.Oorderdate as ORDERDATE, vod.Opayprice as ORDERPAYPRICE, ");
                    sb.Append("(select TAGtitle from UTag inner join USpecification s on TAGid=SPtitletagid where s.SPguid=vom.mokspecid) as SPEC ");
                    sb.Append("FROM V_OrderData vod JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    sb.Append("WHERE 1=1 ");
                    if (querytype == "waiting" || querytype == "history")
                    {
                        switch (querychoice)
                        {
                            case "querytxid":
                                sb.Append("AND vod.Otxid ='" + querycontent + "' ");
                                break;
                            case "querycashid":
                                sb.Append("AND vod.Ocashid ='" + querycontent + "' ");
                                break;
                            case "queryemail":
                                sb.Append("AND vod.Memail ='" + querycontent + "' ");
                                break;
                            case "querymname":
                                sb.Append("AND vod.MDlastname + vod.MDfirstname ='" + querycontent + "' ");
                                break;
                            default:
                                break;
                        }
                        if(querytype == "waiting")
                            sb.Append("AND vod.Ostatus =" + (int)Config.OrderStatusType.Waiting + " ");
                    }
                    sb.Append("ORDER BY vod.Oorderdate DESC ");

                    var me = _dc.ExecuteQuery<OrderData>(
                        sb.ToString());

                    if (me != null)
                    {
                        orderdataList = me.ToList();
                    }
                    #region
                    //var me = _dc.V_OrderData.OrderByDescending(vod => vod.Oorderdate).AsQueryable();

                    //switch (querytype)
                    //{
                    //    case "waiting":
                    //            me = me.Where(vod => vod.Ostatus == (int)Config.OrderStatusType.Waiting || vod.Ostatus == (int)Config.OrderStatusType.Fail).OrderByDescending(vod => vod.Oorderdate);
                    //        break;                        
                    //    default:
                    //        break;
                    //}

                    //#region 條件篩選
                    //switch (querychoice)
                    //{
                    //    case "queryall":
                    //        break;
                    //    case "querytxid":
                    //        me = me.Where(vod => vod.Otxid == querycontent);
                    //        break;
                    //    case "querycashid":
                    //        me = me.Where(vod => vod.Ocashid == querycontent);
                    //        break;
                    //    case "queryemail":
                    //        me = me.Where(vod => vod.Memail == querycontent);
                    //        break;
                    //    case "querymname":
                    //        me = me.Where(vod => vod.MDlastname + vod.MDfirstname == querycontent);
                    //        break;
                    //    default:
                    //        break;
                    //}
                    //#endregion


                    //if (me != null)
                    //{
                    //    foreach (var item in me)
                    //    {
                    //        OrderData od = new OrderData();
                    //        od.OCASHID = item.Ocashid;
                    //        od.MEMAIL = item.Memail;
                    //        od.OGUID = item.Oguid;
                    //        od.ORDERDATE = item.Oorderdate;
                    //        od.ORDERPRICE = item.Oorderprice;
                    //        od.OTXID = item.Otxid;
                    //        od.OSHIPPINGWAY = item.Oshippingway;
                    //        od.PAYTYPE = item.COpaytype == null ? PayType.Default : (PayType)item.COpaytype;
                    //        od.MNAME = item.MDlastname + item.MDfirstname;
                    //        od.MGUID = item.Mguid;
                            
                    //        orderdataList.Add(od);
                    //    }
                    //}
                    #endregion

                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderdataList", ex.ToString());
            }
            return orderdataList;
        }

        public List<OrderData> GetOrderdataListPayment()
        {
            List<OrderData> orderdataList = new List<OrderData>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {                    
                    StringBuilder sb = new StringBuilder();
                    //sb.Append("SELECT *,  ");
                    //sb.Append("(select TAGtitle from UTag where TAGid = s.SPtitletagid) as SPEC , ");
                    //sb.Append("(select TAGtitle from UTag where TAGid = mt.MKcolortagid) as COLOR ");
                    //sb.Append("FROM V_OrderData vod ");
                    //sb.Append("JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    //sb.Append("left join UProduct p on p.PDmkguid = vom.MOKmkguid ");
                    //sb.Append("left join USpecification s on s.SPpdguid = p.PDguid ");
                    //sb.Append("left join V_MarketTagList mt on mt.MKguid = vom.MOKmkguid ");

                    sb.Append("SELECT *, vod.Oorderprice as ORDERPRICE, (MDlastname + MDfirstname) as MNAME, MOKpdtitle as COLOR, vod.COpaytype as PAYTYPE, ISNULL(MOKcomboid,0) as MOKCOMBOOID, MOKtype as MKTYPE,");
                    sb.Append("(select TAGtitle from UTag inner join USpecification s on TAGid=SPtitletagid where s.SPguid=vom.mokspecid) as SPEC ");
                    sb.Append("FROM V_OrderData vod JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    sb.Append("WHERE 1=1 ");
                    sb.Append("AND vod.Ostatus = {0} ");
                    sb.Append("ORDER BY vod.Otxid DESC, vom.MOKcomboid, vom.MOKprice DESC, vod.Oorderdate DESC ");

                    var me = _dc.ExecuteQuery<OrderData>(
                        sb.ToString(),
                        "3"
                        );

                    #region
                    /*
                    foreach (var item in querydata)
                    {
                        Order o = new Order(item.OGUID);
                        Recipient rc = new Recipient(o.OGUID);
                        //Member m = new Member(item);
                        //SimpleOrder so = new SimpleOrder();

                        o.TXID = item.TXID;
                        o.CASHID = item.CASHID;
                        o.PRICE = item.PRICE;
                        o.PAYPRICE = item.PAYPRICE;
                        o.PW = (PayType)item.PW;
                        o.DATEIN = item.DATEIN;

                        rc.NAME = item.RECIPIENT.NAME;
                        rc.TEL = item.RECIPIENT.TEL;
                        rc.SEX = item.RECIPIENT.SEX == null ? true : item.RECIPIENT.SEX;
                        rc.ZIP = item.RECIPIENT.ZIP;
                        rc.COUNTRY = item.RECIPIENT.COUNTRY;
                        rc.CITY = item.RECIPIENT.CITY;
                        rc.AREA = item.RECIPIENT.AREA;
                        rc.ADDRESS = item.RECIPIENT.ADDRESS;

                        o.RECIPIENT = rc;


                        orderList.Add(o);
                    }*/
                    #endregion


                    if (me != null)
                    {
                        orderdataList = me.ToList();
                        //foreach (var item in me)
                        //{
                        //    OrderData od = new OrderData();
                        //    od.OCASHID = item.OCASHID;
                        //    od.MEMAIL = item.MEMAIL;
                        //    od.OGUID = item.OGUID;
                        //    od.ORDERDATE = item.ORDERDATE;
                        //    od.ORDERPRICE = item.ORDERPRICE;
                        //    od.OTXID = item.OTXID;
                        //    od.OSHIPPINGWAY = item.OSHIPPINGWAY;
                        //    od.PAYTYPE = item.PAYTYPE == null ? PayType.Default : (PayType)item.PAYTYPE;
                        //    //od.MNAME = item.MNAME;
                        //    od.MGUID = item.MGUID;

                        //    Member m = new Member(item.MEMAIL);
                        //    od.MNAME = m.FIRSTNAME + m.LASTNAME;

                        //    Order o = new Order(item.OGUID);
                        //    o.GetOrderMarketList();
                        //    o.GetRecipient();

                        //    //od.ORDERMARKETLIST = o.MLIST;
                        //    //od.RECIPIENT = o.RECIPIENT;

                        //    orderdataList.Add(od);
                        //}
                    }


                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderdataListPayment", ex.ToString());
            }
            return orderdataList;
        }

        public List<OrderData> GetOrderdataListReturning(DateTime batchstartdate, DateTime batchenddate)
        {
            List<OrderData> orderdataList = new List<OrderData>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    StringBuilder sb = new StringBuilder();
                    //sb.Append("SELECT *,  ");
                    //sb.Append("(select TAGtitle from UTag where TAGid = s.SPtitletagid) as SPEC , ");
                    //sb.Append("(select TAGtitle from UTag where TAGid = mt.MKcolortagid) as COLOR ");
                    //sb.Append("FROM V_OrderData vod ");
                    //sb.Append("JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    //sb.Append("left join UProduct p on p.PDmkguid = vom.MOKmkguid ");
                    //sb.Append("left join USpecification s on s.SPpdguid = p.PDguid ");
                    //sb.Append("left join V_MarketTagList mt on mt.MKguid = vom.MOKmkguid ");

                    sb.Append("SELECT *, vod.Oorderprice as ORDERPRICE, (MDlastname + MDfirstname) as MNAME, MOKpdtitle as COLOR, vod.COpaytype as PAYTYPE, ");
                    sb.Append("(select TAGtitle from UTag inner join USpecification s on TAGid=SPtitletagid where s.SPguid=vom.mokspecid) as SPEC ");
                    sb.Append("FROM V_OrderData vod JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    sb.Append("WHERE 1=1 ");
                    sb.Append("AND (vod.Ostatus = {0} OR vod.Ostatus = {1}) ");
                    sb.Append("AND vod.Ostatusdatein BETWEEN {2} AND {3} ");
                    sb.Append("ORDER BY vod.Oorderdate DESC ");

                    var me = _dc.ExecuteQuery<OrderData>(
                        sb.ToString(),
                        "2", "4",
                        batchstartdate,
                        batchenddate
                        );

                    #region
                    /*
                    foreach (var item in querydata)
                    {
                        Order o = new Order(item.OGUID);
                        Recipient rc = new Recipient(o.OGUID);
                        //Member m = new Member(item);
                        //SimpleOrder so = new SimpleOrder();

                        o.TXID = item.TXID;
                        o.CASHID = item.CASHID;
                        o.PRICE = item.PRICE;
                        o.PAYPRICE = item.PAYPRICE;
                        o.PW = (PayType)item.PW;
                        o.DATEIN = item.DATEIN;

                        rc.NAME = item.RECIPIENT.NAME;
                        rc.TEL = item.RECIPIENT.TEL;
                        rc.SEX = item.RECIPIENT.SEX == null ? true : item.RECIPIENT.SEX;
                        rc.ZIP = item.RECIPIENT.ZIP;
                        rc.COUNTRY = item.RECIPIENT.COUNTRY;
                        rc.CITY = item.RECIPIENT.CITY;
                        rc.AREA = item.RECIPIENT.AREA;
                        rc.ADDRESS = item.RECIPIENT.ADDRESS;

                        o.RECIPIENT = rc;


                        orderList.Add(o);
                    }*/
                    #endregion


                    if (me != null)
                    {
                        orderdataList = me.ToList();
                        //foreach (var item in me)
                        //{
                        //    OrderData od = new OrderData();
                        //    od.OCASHID = item.OCASHID;
                        //    od.MEMAIL = item.MEMAIL;
                        //    od.OGUID = item.OGUID;
                        //    od.ORDERDATE = item.ORDERDATE;
                        //    od.ORDERPRICE = item.ORDERPRICE;
                        //    od.OTXID = item.OTXID;
                        //    od.OSHIPPINGWAY = item.OSHIPPINGWAY;
                        //    od.PAYTYPE = item.PAYTYPE == null ? PayType.Default : (PayType)item.PAYTYPE;
                        //    //od.MNAME = item.MNAME;
                        //    od.MGUID = item.MGUID;

                        //    Member m = new Member(item.MEMAIL);
                        //    od.MNAME = m.FIRSTNAME + m.LASTNAME;

                        //    Order o = new Order(item.OGUID);
                        //    o.GetOrderMarketList();
                        //    o.GetRecipient();

                        //    //od.ORDERMARKETLIST = o.MLIST;
                        //    //od.RECIPIENT = o.RECIPIENT;

                        //    orderdataList.Add(od);
                        //}
                    }


                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderdataListReturning", ex.ToString());
            }
            return orderdataList;
        }

        public bool CreateOrderBatchData(string batchid, Guid oguid, DateTime batchdate)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    var a = _dc.UOrderBatch.Where(ob => ob.OBoguid == oguid).Count();
                    if (a == 0)
                    {
                        _dc.SP_CreateOrderBatchData(batchid, oguid, batchdate);
                        return true;
                    }                    
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "createOrderBatchData", ex.ToString());
            }
            return false;
        }

        public List<OrderBatch> GetShipBatch(DateTime batchstartdate, DateTime batchenddate)
        {
            List<OrderBatch> batchList = new List<OrderBatch>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT OBbatchid, OBdatein FROM UOrderBatch WHERE OBdatein BETWEEN {0} AND {1} GROUP BY OBbatchid, OBdatein ");
                    var me = _dc.ExecuteQuery<OrderBatch>(
                        sb.ToString(),
                        batchstartdate,
                        batchenddate
                        );

                    if (me != null)
                    {
                        batchList = me.ToList();
                    }
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetShipBatch", ex.ToString());
            }

            return batchList;
        }

        public List<OrderData> GetOrderdataListShipBatch(string batchid, string sort)
        {
            List<OrderData> orderdataList = new List<OrderData>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT *, vod.Oorderprice as ORDERPRICE, (MDlastname + MDfirstname) as MNAME, MOKpdtitle as COLOR, vod.COpaytype as PAYTYPE,ISNULL(MOKcomboid,0) as MOKCOMBOOID, MOKtype as MKTYPE, ");
                    sb.Append("vod.Oorderdate as ORDERDATE, vod.Opayprice as ORDERPAYPRICE, ");
                    sb.Append("(SELECT TOP 1 OSdatein FROM UOrderStatus WHERE OSOguid = vod.Oguid AND OSstatus=3 ORDER BY OSdatein DESC) as ORDERPAYDATE, ");
                    sb.Append("(SELECT TAGtitle FROM UTag INNER JOIN USpecification s ON TAGid=SPtitletagid WHERE s.SPguid=vom.mokspecid) as SPEC ");
                    sb.Append("FROM V_OrderData vod JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid ");
                    sb.Append("JOIN UOrderBatch ob ON ob.OBoguid = vod.Oguid ");                    
                    sb.Append("WHERE 1=1 ");
                    sb.Append("AND (vod.Ostatus = {0} OR vod.Ostatus = {1}) ");
                    sb.Append("AND ob.OBbatchid = {2} ");
                    if (sort != "")                    
                        sb.Append("ORDER BY " + sort);

                    var me = _dc.ExecuteQuery<OrderData>(
                        sb.ToString(),
                        "9", "8", batchid
                        );

                  

                    if (me != null)
                    {
                        orderdataList = me.ToList();
                    }


                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderdataListShipBatch", ex.ToString());
            }
            return orderdataList;
        }

        public List<OrderData> GetOrderdata(Guid oguid)
        {
            List<OrderData> orderdataList = new List<OrderData>();
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.Append("SELECT *, vod.Oorderprice as ORDERPRICE, (MDlastname + MDfirstname) as MNAME, vod.COpaytype as PAYTYPE ");//, MOKpdtitle as COLOR
                    //sb.Append("(select TAGtitle from UTag inner join USpecification s on TAGid=SPtitletagid where s.SPguid=vom.mokspecid) as SPEC ");
                    sb.Append("FROM V_OrderData vod ");//JOIN V_OrderMarketList vom ON vod.Oguid = vom.MOKOguid
                    sb.Append("join UOrderBatch ob on ob.OBoguid = vod.Oguid ");

                    sb.Append("WHERE 1=1 ");
                    sb.Append("AND (vod.Ostatus = {0} OR vod.Ostatus = {1}) ");
                    sb.Append("AND vod.Oguid = {2} ");
                    var me = _dc.ExecuteQuery<OrderData>(
                        sb.ToString(),
                        "9", "8",
                        oguid
                        );



                    if (me != null)
                    {
                        orderdataList = me.ToList();
                        
                    }


                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "GetOrderdata", ex.ToString());
            }
            return orderdataList;
        }

        public bool UpdateOrderShippingnum(Guid oguid, string shippingnum)
        {
            try
            {
                using (_dc = new DataClassesMenDataContext(Config.connString))
                {
                    _dc.SP_UptUOShippingnum(shippingnum, oguid);
                    return true;
                }
            }
            catch (Exception ex)
            {
                _log.RecordExceptionFormat(_log.DBExceptionPath, "UpdateOrderShippingnum", ex.ToString());
            }
            return false;
        }




    }
}
